{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 实现国家电网招标数据的抓取，解压数据，文件名解码成中文，合并成数据表格，写入数据库"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 标书下载文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "http://ecp.sgcc.com.cn/ecp1.0/project_list.jsp?site=global&column_code=014001001&project_type=1\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-08-07']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-31']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-29']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-27']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-20']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   正在评标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-20']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-15']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-15']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   正在评标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-10']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-07-02']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-30']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-24']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-23']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-12']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-12']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-11']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-10']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   正在评标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-05']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   正在评标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-04']\n",
      "['\\r\\n\\t\\t   \\r\\n\\t\\t   已经截标\\r\\n\\t\\t  \\r\\n\\t\\t    ']\n",
      "['2020-06-03']\n",
      "本页面共20项标书\n",
      "本页成功下载20件标书\n",
      "第1页下载完成\n",
      "最终下载合格标书20项目\n"
     ]
    }
   ],
   "source": [
    "#下载文件\n",
    "import requests\n",
    "from lxml import etree\n",
    "import re,time\n",
    "import urllib\n",
    "from urllib  import request\n",
    "import os\n",
    "#一共使用了5个计数变量\n",
    "#计数变量\n",
    "c = 0\n",
    "f = 0\n",
    "g = 0\n",
    "# try:\n",
    "#     os.mkdir('./savefiles')\n",
    "#     print('创建爬虫下载存储文件')\n",
    "# except:\n",
    "#     print('文件已经存在')\n",
    "\n",
    "page = 1\n",
    "#下载 ～ 页数据\n",
    "#http://ecp.sgcc.com.cn/ecp1.0/project_list.jsp?site=global&column_code=014001001&project_type=1&company_id=00&status=6&project_name=all&pageNo=2\n",
    "for i in range(1,2):\n",
    "    #抓取的所有页面的某一页\n",
    "#     url ='http://ecp.sgcc.com.cn/ecp1.0/project_list.jsp?site=global&column_code=014001001&project_type=1&company_id=&status=&project_name=&pageNo=%d'%i\n",
    "    url = 'http://ecp.sgcc.com.cn/ecp1.0/project_list.jsp?site=global&column_code=014001001&project_type=1'\n",
    "    print(url)\n",
    "    #构造请求头\n",
    "    ss= 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:6.0) Gecko/20100101 Firefox/6.0'\n",
    "    headers = { \"User-Agent\": ss}\n",
    "    #第一次请求，开始请求某一页\n",
    "    response = requests.get(url=url,headers=headers)\n",
    "    #print(response.text）\n",
    "    #返回的数据做成xpath对象，便于下一步xpath匹配\n",
    "    xpathtree = etree.HTML(response.text)\n",
    "    #匹某一标书的标签内容，共多少标书\n",
    "    res = xpathtree.xpath('//tr[@align=\"left\"]')\n",
    "    #获取\n",
    "    a= 0\n",
    "    b= 0\n",
    "    del res[0]\n",
    "    \n",
    "#每一个标书页面开始循环\n",
    "    for resi in res:        \n",
    "        #打印标书的状态\n",
    "        print(resi.xpath('./td[@class=\"black40\"][1]/text()'))\n",
    "        #打印标书的时间\n",
    "        print(resi.xpath('./td[@class=\"black40\"][4]/text()'))\n",
    "        #获取标书的可以下载页面的详情url\n",
    "        str1 = resi.xpath('./td[@class=\"black40\"]/a/@onclick')\n",
    "        #print(str1)\n",
    "        num = re.search('showProjectDetail\\((.*?),\\'(.*?)\\'\\)',str1[0])\n",
    "        #注意这里是使用正则匹配标书的下载地址的的数字\n",
    "        num = num.group(2)\n",
    "        #地址拼接\n",
    "        url2  = 'http://ecp.sgcc.com.cn/ecp1.0/html/project/014001001/%s.html'%num\n",
    "        #print(url2)\n",
    "        #第二次请求，最终下载文件页面的地址\n",
    "        response2 = requests.get(url=url2, headers=headers)\n",
    "        original= 'http://ecp.sgcc.com.cn'\n",
    "        #可以下载的页面，检测下载地址是不是可以下载\n",
    "        if len(original+etree.HTML(response2.text).xpath('//td/a/@href')[0])>45:\n",
    "            #如果下载地址的字符超过45，表示是可以尝试下载的地址\n",
    "            #标书的状态\n",
    "            projectstatus = etree.HTML(response2.text).xpath('//td[2]/text()')[0]\n",
    "            #re.search('招标结束',projectstatus)\n",
    "            #只下载招标结束的文件         \n",
    "            #匹配出标书的状态\n",
    "            if re.search('(\\S+.*?)', projectstatus):\n",
    "                #获取标书的状态\n",
    "                rename = re.search('(\\S+.*?)', projectstatus).group(1)         \n",
    "                #匹配标书的名字\n",
    "                projectname =   etree.HTML(response2.text).xpath('//td[2]/text()')[2]\n",
    "                #拼接标书的名字\n",
    "                projectname= rename+projectname\n",
    "                #第三次请求，获取标书的最终下载地址\n",
    "                downloadfileurl = original+etree.HTML(response2.text).xpath('//td/a/@href')[0]\n",
    "                #print('projectstatus',projectstatus)\n",
    "                #print('projectname',projectname)\n",
    "                #print('downloadfileurl',downloadfileurl)\n",
    "                #设置下载速度间隔2秒\n",
    "                #开始下载文件代码\n",
    "#                 try:\n",
    "                response = requests.get(url=downloadfileurl, headers=headers)\n",
    "                with open('./savefiles/%s.zip' % projectname, 'wb') as v:\n",
    "                    v.write(response.content)\n",
    "                time.sleep(1)\n",
    "                b+=1\n",
    "                f+=1\n",
    "                \n",
    "#                 try:\n",
    "#                     urllib.request.urlretrieve(url=downloadfileurl,filename='./savefiles/%s.zip'%projectname)\n",
    "#                     # 下载成功一次标书，b增加1，统计本页标书下载成功的数量\n",
    "#                     b+=1\n",
    "#                     #下载成功一次标书，f增加1，统计成功下载标书的总数量的计数变量\n",
    "#                     f+=1\n",
    "#                     #time.sleep(2)\n",
    "#                 except:\n",
    "#                     #下载失败一次标书，g增加1，统计下载失败标书的总数量的计数变量\n",
    "#                     g+=1\n",
    "#                     print('链接超时下载失败数g',g)\n",
    "#                     #下载失败就进行下一次下载，不磨叽\n",
    "#                     continue\n",
    "            # else:\n",
    "            #     print('非招标结束状态')\n",
    "            else:\n",
    "                print('此标的文件无法下载',url2)\n",
    "#         a代表本页共有多少标书\n",
    "        a+=1\n",
    "#     代表到多少页\n",
    "    c+=1\n",
    "    print('本页面共%d项标书'%a)\n",
    "    print('本页成功下载%d件标书'%b)\n",
    "    print('第%d页下载完成'%c)\n",
    "    \n",
    "print('最终下载合格标书%d项目'%f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 解压文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "查看所有文件数量 175\n",
      "i ./savefiles/正在评标中国电力科学研究院有限公司2020年第四批物资类招标项目.zip\n",
      "i ./savefiles/正在评标国网青海省电力公司2020年第一次物资类招标采购项目.zip\n",
      "i ./savefiles/正在评标国网江苏省电力有限公司2020年第二批物资招标采购.zip\n",
      "i ./savefiles/评标结束国家电网有限公司信息化项目2020年第一次设备招标采购-信息化硬件.zip\n",
      "i ./savefiles/已经截标国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目-招标公告.zip\n",
      "i ./savefiles/评标结束国家电网有限公司输变电项目2020年第一次变电设备（含电缆）招标采购-组合电器.zip\n",
      "i ./savefiles/已经截标英大长安保险经纪有限公司2020年第二批公开招标（补充）项目.zip\n",
      "i ./savefiles/已经截标山东电力高等专科学校2020年第二批物资类招标项目.zip\n",
      "i ./savefiles/评标结束国网辽宁省电力有限公司2020年第一批物资招标采购项目.zip\n",
      "i ./savefiles/招标结束国网天津市电力公司2020年四月新增物资招标采购项目.zip\n"
     ]
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "#解压文件\n",
    "import os,re\n",
    "import shutil\n",
    "\n",
    "import zipfile\n",
    "lis0  = os.listdir('./savefiles')\n",
    "lis1  = []\n",
    "for f in lis0:\n",
    "    if not  f.startswith('.'):\n",
    "        lis1.append(f)\n",
    "#读取savefiles文件夹内的所有的文件\n",
    "#去掉.开头的隐藏文件\n",
    "print('查看所有文件数量',len(lis1))\n",
    "try:\n",
    "    os.mkdir('./unpacked')\n",
    "except:\n",
    "    shutil.rmtree('./unpacked') \n",
    "    os.mkdir('./unpacked')\n",
    "    print('原文件已经删除，并继续创建')\n",
    "#遍历所有文件\n",
    "h = 0\n",
    "k = 0\n",
    "for i in lis1[:10]:\n",
    "    try:\n",
    "        #匹配获取文件的名字\n",
    "        name = re.search('(.*?).zip',i)\n",
    "        #拿到文件名字\n",
    "        name = name.group(1)\n",
    "        #文件名进行与路径的拼接\n",
    "        # print(os.path.join('./test3',i))\n",
    "        i = os.path.join('./savefiles',i)\n",
    "        #print(i)\n",
    "        #zip_file = zipfile.ZipFile(path)\n",
    "        print('i',i)\n",
    "        #解压文件\n",
    "        zip_file_contents = zipfile.ZipFile(i, 'r')\n",
    "      \n",
    "    except:\n",
    "        k+=1\n",
    "        #print('第%d个文件解压失败'%h,)\n",
    "        continue\n",
    "    #遍历要解压文件内的所有数据\n",
    "    for file in zip_file_contents.namelist():\n",
    "         try:\n",
    "             #进行编码，这块头大，编码问题\n",
    "             filename = file.encode('cp437').decode('gbk')\n",
    "             #先使用cp437编码，然后再使用gbk解码\n",
    "             #print(filename)\n",
    "             #print(file)\n",
    "             #开始解压                              \n",
    "             zip_file_contents.extract(file,'./unpacked/%s'%name)#解压缩ZIP文件\n",
    "             #创建名字\n",
    "             file ='./unpacked/{}/{}'.format(name,file)\n",
    "             filename = './unpacked/{}/{}'.format(name,filename)\n",
    "             #重命名文件\n",
    "             #print('第%d个文件解压成功'%h) \n",
    "             #windows 系统不需要os.rename(file,filename)，注释即可  \n",
    "             os.rename(file,filename)\n",
    "         except:\n",
    "            continue\n",
    "\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "ename": "FileNotFoundError",
     "evalue": "[Errno 2] No such file or directory: './╣·╝╥╡τ═°╙╨╧▐╣½╦╛╨┼╧ó═¿╨┼╖╓╣½╦╛2020─Ω╡┌╚²┼·╬∩╫╩└α╣½┐¬╒╨▒Ω╧ε─┐ú¿╬∩╫╩ú⌐-╣½╕µ/╣·╝╥╡τ═°╙╨╧▐╣½╦╛╨┼╧ó═¿╨┼╖╓╣½╦╛2020─Ω╡┌╚²┼·╬∩╫╩└α╣½┐¬╒╨▒Ω╧ε─┐ú¿╬∩╫╩ú⌐.doc' -> './国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目（物资）-公告/国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目（物资）.doc'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mFileNotFoundError\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-130-8122900379ad>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      7\u001b[0m         \u001b[0miname\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'./{}'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0miname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;31m#         print(filename)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 9\u001b[0;31m         \u001b[0mos\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrename\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mfilei\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0miname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     10\u001b[0m \u001b[0;31m#         print(os.path.realpath(file))\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mFileNotFoundError\u001b[0m: [Errno 2] No such file or directory: './╣·╝╥╡τ═°╙╨╧▐╣½╦╛╨┼╧ó═¿╨┼╖╓╣½╦╛2020─Ω╡┌╚²┼·╬∩╫╩└α╣½┐¬╒╨▒Ω╧ε─┐ú¿╬∩╫╩ú⌐-╣½╕µ/╣·╝╥╡τ═°╙╨╧▐╣½╦╛╨┼╧ó═¿╨┼╖╓╣½╦╛2020─Ω╡┌╚²┼·╬∩╫╩└α╣½┐¬╒╨▒Ω╧ε─┐ú¿╬∩╫╩ú⌐.doc' -> './国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目（物资）-公告/国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目（物资）.doc'"
     ]
    }
   ],
   "source": [
    "# 解压缩后发现还有一个资料夹, 但无法成功 rename 其中已经解压完成的档案\n",
    "with zipfile.ZipFile('./savefiles/已经截标国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目-招标公告.zip','r') as myzip:\n",
    "    for i in myzip.namelist():\n",
    "        iname = i.encode('cp437').decode('gbk')\n",
    "        myzip.extract(i,'/Users/hh/Document_lc/PythonWorkspace/CSDN課程資料/7th Week 互联网业务指标')\n",
    "        filei = './{}'.format(i)\n",
    "        iname = './{}'.format(iname)\n",
    "#         print(filename)\n",
    "        os.rename(filei,iname)\n",
    "#         print(os.path.realpath(file))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "900000000000018158_╒╨▒Ω╣½╕µ.rar\n"
     ]
    }
   ],
   "source": [
    "# 解压缩后发现还有一个 rar, rar 无法用 ZipFile 解压 \n",
    "with zipfile.ZipFile('./savefiles/已经截标英大长安保险经纪有限公司2020年第二批公开招标（补充）项目.zip') as myzip:\n",
    "    for i in myzip.namelist():\n",
    "        print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<zipfile.ZipFile filename='./savefiles/招标结束国网天津市电力公司2020年四月新增物资招标采购项目.zip' mode='r'>"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zip_file_contents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 把状态写入t x t  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import os, re\n",
    "import zipfile\n",
    "import pandas as pd\n",
    "# 显示所有行列\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "#把所有标书的状态全部存储进一个txt\n",
    "testlist = os.listdir('./unpacked')\n",
    "for i in  testlist:\n",
    "    #print(re.match('(\\w){4}',i))\n",
    "    #匹配所有文件的前面的4个字符\n",
    "    if re.match('(\\w){4}',i):  \n",
    "        stauts = re.match('(\\w){4}',i).group()\n",
    "        #写进t x t\n",
    "        fileswrite = open('./unpacked/%s/status.txt'%i,'w')\n",
    "        fileswrite.write(stauts)\n",
    "        fileswrite.close()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 货物清单数据表合并，添加新状态，去除重复数据，存储数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['./unpacked/招标结束国网天津市电力公司2020年四月新增物资招标采购项目',\n",
       " './unpacked/评标结束国家电网有限公司输变电项目2020年第一次变电设备（含电缆）招标采购-组合电器',\n",
       " './unpacked/评标结束国家电网有限公司信息化项目2020年第一次设备招标采购-信息化硬件',\n",
       " './unpacked/已经截标山东电力高等专科学校2020年第二批物资类招标项目',\n",
       " './unpacked/评标结束国网辽宁省电力有限公司2020年第一批物资招标采购项目',\n",
       " './unpacked/正在评标中国电力科学研究院有限公司2020年第四批物资类招标项目',\n",
       " './unpacked/已经截标英大长安保险经纪有限公司2020年第二批公开招标（补充）项目',\n",
       " './unpacked/正在评标国网青海省电力公司2020年第一次物资类招标采购项目',\n",
       " './unpacked/已经截标国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目-招标公告',\n",
       " './unpacked/正在评标国网江苏省电力有限公司2020年第二批物资招标采购']"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -*- coding:utf-8 -*-\n",
    "import os\n",
    "import os, re\n",
    "import zipfile\n",
    "import pandas as pd\n",
    "# 显示所有行列\n",
    "pd.set_option('display.max_columns', None)\n",
    "pd.set_option('display.max_rows', None)\n",
    "\n",
    "lis3 = os.listdir('./unpacked')\n",
    "lis4 = []\n",
    "\n",
    "for f in lis3:\n",
    "    if not f.startswith('.'):\n",
    "        f = os.path.join('./unpacked', f)\n",
    "        lis4.append(f)\n",
    "lis4\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./unpacked/评标结束国家电网有限公司输变电项目2020年第一次变电设备（含电缆）招标采购-组合电器\n",
      "评标结束\n",
      "./unpacked/评标结束国家电网有限公司输变电项目2020年第一次变电设备（含电缆）招标采购-组合电器/990000000000126036第1章_货物清单（附件）.xls\n",
      "这个excelsheet数 1\n",
      "./unpacked/评标结束国家电网有限公司信息化项目2020年第一次设备招标采购-信息化硬件\n",
      "评标结束\n",
      "./unpacked/评标结束国家电网有限公司信息化项目2020年第一次设备招标采购-信息化硬件/990000000000126405第1章_货物清单（附件）.xls\n",
      "这个excelsheet数 1\n",
      "./unpacked/已经截标山东电力高等专科学校2020年第二批物资类招标项目\n",
      "./unpacked/评标结束国网辽宁省电力有限公司2020年第一批物资招标采购项目\n",
      "评标结束\n",
      "./unpacked/评标结束国网辽宁省电力有限公司2020年第一批物资招标采购项目/货物清单.xls\n",
      "这个excelsheet数 58\n",
      "./unpacked/正在评标中国电力科学研究院有限公司2020年第四批物资类招标项目\n",
      "正在评标\n",
      "./unpacked/正在评标中国电力科学研究院有限公司2020年第四批物资类招标项目/货物清单.xls\n",
      "这个excelsheet数 20\n",
      "./unpacked/已经截标英大长安保险经纪有限公司2020年第二批公开招标（补充）项目\n",
      "./unpacked/正在评标国网青海省电力公司2020年第一次物资类招标采购项目\n",
      "正在评标\n",
      "./unpacked/正在评标国网青海省电力公司2020年第一次物资类招标采购项目/货物清单.xls\n",
      "这个excelsheet数 15\n",
      "./unpacked/已经截标国家电网有限公司信息通信分公司2020年第三批物资类公开招标项目-招标公告\n",
      "./unpacked/正在评标国网江苏省电力有限公司2020年第二批物资招标采购\n",
      "正在评标\n",
      "./unpacked/正在评标国网江苏省电力有限公司2020年第二批物资招标采购/货物清单.xls\n",
      "这个excelsheet数 59\n"
     ]
    }
   ],
   "source": [
    "#去掉.开头的隐藏文件\n",
    "k = 0\n",
    "\n",
    "# 存储sheetname的列表\n",
    "listsheet = []\n",
    "#遍历所有文件\n",
    "for i in lis4[1:]:\n",
    "    print(i)\n",
    "    for j in os.listdir(i):\n",
    "        #列出每个文件内的具体内容\n",
    "        if re.search('[货物清单].*?.xls', j):\n",
    "            #匹配有没有货物清单的excel表\n",
    "            final = os.path.join(i, j)\n",
    "            #如果有就进行拼接路径\n",
    "            #print('能检测到货物清单的文件夹',i)\n",
    "            #并且获取文件的投标状态\n",
    "            statusread = open('%s/status.txt'%i,'r')\n",
    "            status1 = statusread.read()\n",
    "            #打印路径和状态查看\n",
    "            print(status1)\n",
    "            print(final)\n",
    "            #读取货物清单的excel\n",
    "            excel_reader = pd.ExcelFile(final) \n",
    "            #每个excel 有多个 sheet\n",
    "            sheet_names = excel_reader.sheet_names      \n",
    "            print('这个excelsheet数',len(sheet_names))\n",
    "            #列出所有sheet的名字\n",
    "            k+=1\n",
    "            for i in range(len(sheet_names)):\n",
    "                #读取每个sheet\n",
    "                df1 = excel_reader.parse(sheet_name=sheet_names[i],skiprows=1)\n",
    "                #skiprows=1的意思是跳过excel的第一行，因为第一行并不是我们的表头，表格有毛病\n",
    "                df1['状态'] = status1\n",
    "                #读取出来的数据，df1创建一个新的字段‘状态’\n",
    "                listsheet.append(df1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>包号</th>\n",
       "      <th>网省采购申请行号</th>\n",
       "      <th>项目单位</th>\n",
       "      <th>需求单位</th>\n",
       "      <th>项目名称</th>\n",
       "      <th>工程电压等级</th>\n",
       "      <th>物资名称</th>\n",
       "      <th>物资描述</th>\n",
       "      <th>单位</th>\n",
       "      <th>数量</th>\n",
       "      <th>交货日期</th>\n",
       "      <th>交货地点</th>\n",
       "      <th>备注</th>\n",
       "      <th>技术规范ID</th>\n",
       "      <th>状态</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>包1</td>\n",
       "      <td>100036532300030</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司沈阳供电公司</td>\n",
       "      <td>东场66kV变电站新建工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>118000</td>\n",
       "      <td>2020-06-20</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A475-500087802-00004</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>包1</td>\n",
       "      <td>100028355300010</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司朝阳供电公司</td>\n",
       "      <td>东山66千伏变电站改造工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>37000</td>\n",
       "      <td>2020-07-18</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A566-500087802-00011</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>包1</td>\n",
       "      <td>100036304300010</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司阜新供电公司</td>\n",
       "      <td>阜新来虎220千伏变电站新建工程</td>\n",
       "      <td>220kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>74500</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>买方指定仓库地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A414-500087802-00008</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>包1</td>\n",
       "      <td>100036527300030</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司沈阳供电公司</td>\n",
       "      <td>核心66kV变电站新建工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>118000</td>\n",
       "      <td>2020-07-01</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A475-500087802-00006</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>包1</td>\n",
       "      <td>100022261900410</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司辽阳供电公司</td>\n",
       "      <td>五星66千伏变电站新建工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>39850</td>\n",
       "      <td>2020-10-02</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2228-500087802-00001</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>包1</td>\n",
       "      <td>100036316100010</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司盘锦供电公司</td>\n",
       "      <td>西安66千伏变电站改造工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>36600</td>\n",
       "      <td>2020-07-15</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A464-500087802-00003</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>包1</td>\n",
       "      <td>100028355500010</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司朝阳供电公司</td>\n",
       "      <td>小塔子66千伏变电站新建工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>37000</td>\n",
       "      <td>2020-07-18</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A566-500087802-00010</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>包1</td>\n",
       "      <td>100036527400020</td>\n",
       "      <td>辽宁省电力有限公司</td>\n",
       "      <td>辽宁省电力有限公司沈阳供电公司</td>\n",
       "      <td>中街66千伏变电站新建工程</td>\n",
       "      <td>66kV</td>\n",
       "      <td>H型钢,125×125,Q345</td>\n",
       "      <td>H型钢-型号mm:125×125,材质:Q345</td>\n",
       "      <td>千克</td>\n",
       "      <td>118000</td>\n",
       "      <td>2020-08-31</td>\n",
       "      <td>施工现场地面交货</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A475-500087802-00002</td>\n",
       "      <td>评标结束</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   包号         网省采购申请行号       项目单位             需求单位              项目名称 工程电压等级  \\\n",
       "0  包1  100036532300030  辽宁省电力有限公司  辽宁省电力有限公司沈阳供电公司     东场66kV变电站新建工程   66kV   \n",
       "1  包1  100028355300010  辽宁省电力有限公司  辽宁省电力有限公司朝阳供电公司     东山66千伏变电站改造工程   66kV   \n",
       "2  包1  100036304300010  辽宁省电力有限公司  辽宁省电力有限公司阜新供电公司  阜新来虎220千伏变电站新建工程  220kV   \n",
       "3  包1  100036527300030  辽宁省电力有限公司  辽宁省电力有限公司沈阳供电公司     核心66kV变电站新建工程   66kV   \n",
       "4  包1  100022261900410  辽宁省电力有限公司  辽宁省电力有限公司辽阳供电公司     五星66千伏变电站新建工程   66kV   \n",
       "5  包1  100036316100010  辽宁省电力有限公司  辽宁省电力有限公司盘锦供电公司     西安66千伏变电站改造工程   66kV   \n",
       "6  包1  100028355500010  辽宁省电力有限公司  辽宁省电力有限公司朝阳供电公司    小塔子66千伏变电站新建工程   66kV   \n",
       "7  包1  100036527400020  辽宁省电力有限公司  辽宁省电力有限公司沈阳供电公司     中街66千伏变电站新建工程   66kV   \n",
       "\n",
       "               物资名称                      物资描述  单位      数量        交货日期  \\\n",
       "0  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克  118000  2020-06-20   \n",
       "1  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克   37000  2020-07-18   \n",
       "2  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克   74500  2020-06-30   \n",
       "3  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克  118000  2020-07-01   \n",
       "4  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克   39850  2020-10-02   \n",
       "5  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克   36600  2020-07-15   \n",
       "6  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克   37000  2020-07-18   \n",
       "7  H型钢,125×125,Q345  H型钢-型号mm:125×125,材质:Q345  千克  118000  2020-08-31   \n",
       "\n",
       "         交货地点  备注                技术规范ID    状态  \n",
       "0    施工现场地面交货 NaN  A475-500087802-00004  评标结束  \n",
       "1    施工现场地面交货 NaN  A566-500087802-00011  评标结束  \n",
       "2  买方指定仓库地面交货 NaN  A414-500087802-00008  评标结束  \n",
       "3    施工现场地面交货 NaN  A475-500087802-00006  评标结束  \n",
       "4    施工现场地面交货 NaN  2228-500087802-00001  评标结束  \n",
       "5    施工现场地面交货 NaN  A464-500087802-00003  评标结束  \n",
       "6    施工现场地面交货 NaN  A566-500087802-00010  评标结束  \n",
       "7    施工现场地面交货 NaN  A475-500087802-00002  评标结束  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "listsheet[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "k个货物清单完成读取 6\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['包号', '网省采购申请行号', '项目单位', '需求单位', '项目名称', '工程电压等级', '物资名称', '物资描述',\n",
       "       '单位', '数量', '交货日期', '交货地点', '备注', '技术规范ID', '状态'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "                \n",
    "print('k个货物清单完成读取',k)   \n",
    "                #把所有的读取出来的数据的名字存储进 listsheet\n",
    "#使用concat 把所有的表进行垂直拼接\n",
    "df2 = pd.concat(listsheet)\n",
    "#拼接出来的df2 列数很多，我们只要重要的14列，与1列我们添加的 状态列\n",
    "headname = ['包号','网省采购申请行号','项目单位', '需求单位','项目名称','工程电压等级','物资名称', '物资描述', '单位', '数量', '交货日期', '交货地点', '备注', '技术规范ID', '状态']\n",
    "#取出想要的列\n",
    "df3 = df2[headname]\n",
    "#转成csv 保存\n",
    "df3.to_csv('test3.csv')\n",
    "df3.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 问题：\n",
    "1. try except 为何在第一段（下载标书）的code 里不能用\n",
    "2. 解压文件时，遇到解压缩后的档案里又有另一个资料架或另一个 rar档的做法\n",
    "3. zip 不能直接更名？"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
